package com.vinfai.poi;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;


public class ReadExcelToDB {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		ReadExcelToDB exp = new ReadExcelToDB();
		List<DiagICD10> diags = exp.readExcel("F:\\icd10.xls");
		System.out.println(diags.size());
		for(DiagICD10 diag : diags){
			System.out.println(diag);
		}
		updateToTable(diags);
		//updateToTable2(diags);
		//exp.exportToExcel(diags);
		
	} 
	
	
	public static void updateToTable(List<DiagICD10> items){
		Connection conn = DBUtil.getConnection();
		System.out.println(conn);
		PreparedStatement ps = null;
		String sql = "INSERT INTO DIAG_DEF_ICD10(CODE,NAME,SHORTCUT1,GENDER,TURNTO) VALUES(?,?,?,?,?)";
		try {
			conn.setAutoCommit(false); //不自动提交事务
			ps = conn.prepareStatement(sql);
			for(int i=0;i<items.size();i++){
				DiagICD10 item = items.get(i);
				ps.setString(1, item.getCode());
				ps.setString(2, item.getName());
				ps.setString(3, item.getShortCut1());
				ps.setString(4, "");
				ps.setString(5, "");
				ps.addBatch();
			}
			int[] batch = ps.executeBatch();
			System.out.println("===="+batch.length);
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}//异常情况下回滚数据
		}finally{
			DBUtil.close(conn, ps, null);
		}
	}
	
	public static void updateToTable2(List<DiagICD10> items){
		Connection conn = DBUtil.getConnection();
		System.out.println(conn);
		PreparedStatement ps = null;
		String sql = "INSERT INTO DIAG_DEF(CODE,NAME,IS_CHN,SHORTCUT1,ICD10_CODE,ENABLE,ICD10_NAME) VALUES(?,?,'N',?,?,'Y',?)";
		try {
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			for(int i=0;i<items.size();i++){
				DiagICD10 item = items.get(i);
				ps.setString(1, item.getId());
				ps.setString(2, item.getName());
				ps.setString(3, item.getShortCut1());
				ps.setString(4, item.getCode());
				ps.setString(5, item.getName());
				ps.addBatch();
			}
			int[] batch = ps.executeBatch();
			System.out.println("===="+batch.length);
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(conn, ps, null);
		}
		
		
	}
	
	public List<DiagICD10> readExcel(String path){
		if(path==null||"".equals(path.trim())) throw new IllegalArgumentException();
		List<DiagICD10> diags = new ArrayList<DiagICD10>();
		try {
			FileInputStream fis = new FileInputStream(path);
			POIFSFileSystem pis = new POIFSFileSystem(fis);
			HSSFWorkbook wb = new HSSFWorkbook(pis);
			HSSFSheet sheet = wb.getSheetAt(0);
			String name = sheet.getSheetName();
			System.out.println(name);
			System.out.println(sheet.getLastRowNum());
			int count = 0;
			for(Row row : sheet){
				int k = 0;
				DiagICD10 diag = new DiagICD10();
				//for(Cell cell : row)
				//迭代会忽略掉空的CELL, 有问题
				/*for(Iterator<Cell> cellIter = row.cellIterator(); cellIter.hasNext(); ){
					Cell cell = cellIter.next();
					String val = this.getCellStringValue(cell);
					switch(k){
						case 0 : break;
						case 1 : 
							diag.setCode(val);
							break;
						case 2 : 
							diag.setName(val);
							break;
						case 3 :
							diag.setSex(val);
							break;
						case 4 :
							diag.setTurnTo(val);
							break;
						case 5 :
							diag.setShortCut1(val);
							break;
						default : break;
					}
					k++;
				}*/
				//就ICD10码处理
				String val = this.getCellStringValue(row.getCell(0));//index
				String val1 = this.getCellStringValue(row.getCell(1));//icd10
				String val2 = this.getCellStringValue(row.getCell(2));//name
				String val3 = "";//this.getCellStringValue(row.getCell(3));//sex
				String val4 = "";//this.getCellStringValue(row.getCell(4));//trunTo
				String val5 = this.getCellStringValue(row.getCell(3));//shurtCut
				if(val5.length()>40) System.out.println(val5);
				diag.setId(val);
				diag.setCode(val1);
				diag.setName(val2);
				diag.setSex(val3);
				diag.setTurnTo(val4);
				diag.setShortCut1(val5);
				diags.add(diag);
				/*String val = this.getCellStringValue(row.getCell(0));//index
				String val2 = this.getCellStringValue(row.getCell(1)); //icd10
				String val3 = this.getCellStringValue(row.getCell(2));//name
//				String myname = Test.getFirstSpell(val3);
				String myname = getFirstSpellCode(val3);
				//System.out.println(val+";"+val2+";"+val3+";"+myname);
				///count++;
				diag.setId(val);
				diag.setCode(val2);
				diag.setName(val3);
				diag.setShortCut1(myname);
				diags.add(diag);*/
			}
		
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return diags;
	}
	
	
	private String getFirstSpellCode(String cnStr){
		String code = Test.getFirstSpell(cnStr);
		String code1 = "";
		if(code!=null&&!"".equals(code)){
			code1 = code.replaceAll("（", "").replaceAll("）", "").replaceAll("、", "").replaceAll("\\(", "").replaceAll("\\)", "").replaceAll(",", "").replaceAll("\\+", "").replaceAll("，", "").replaceAll(" ", "");
		}
		if(code!=null&&code.equals(code1)){
			System.out.println(cnStr);
		}
		return code1.toUpperCase();
	}
	
	private String getCellStringValue(Cell cell) {    
        String cellValue = ""; 
        if(cell==null) return cellValue;
        switch (cell.getCellType()) {    
        case HSSFCell.CELL_TYPE_STRING://字符串类型
            cellValue = cell.getStringCellValue();
            if("null".equals(cellValue)||cellValue.trim().equals("")||cellValue.trim().length()<=0)    
                cellValue=""; 
            else cellValue = cellValue.trim();
            break;    
        case HSSFCell.CELL_TYPE_NUMERIC: //数值类型
            cellValue = String.valueOf(cell.getNumericCellValue());    
            break;    
        case HSSFCell.CELL_TYPE_FORMULA: //公式
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);    
            cellValue = String.valueOf(cell.getNumericCellValue());    
            break;    
        case HSSFCell.CELL_TYPE_BLANK:    
            cellValue="";    
            break;    
        case HSSFCell.CELL_TYPE_BOOLEAN:    
            break;    
        case HSSFCell.CELL_TYPE_ERROR:    
            break;    
        default:    
            break;    
        }    
        return cellValue;    
    }   
	
	public void exportToExcel(List<DiagICD10> diags){
		//1.create sheet
		Workbook wb = new HSSFWorkbook();
		Sheet sheet = wb.createSheet("new_icd");
		int rownum = 0;
		for(DiagICD10 diag : diags){
			String index = diag.getId();
			String icd = diag.getCode();
			String name = diag.getName();
			String pym = diag.getShortCut1();
			Row row = sheet.createRow(rownum);
			row.createCell(0).setCellValue(index);
			row.createCell(1).setCellValue(icd);
			row.createCell(2).setCellValue(name);
			row.createCell(3).setCellValue(pym);
			rownum++;
		}
		FileOutputStream fos = null;
		try {
			 fos = new FileOutputStream("F:\\icd10.xls");
			wb.write(fos);
		} catch (IOException e) {
			e.printStackTrace();
		}finally{
			if(fos!=null)
				try {
					fos.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
		}
	
	}
}
class DiagICD10{
	private String id;
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}

	private String code;
	private String name;
	private String shortCut1;
	private String sex;
	private String turnTo;
	public String getCode() {
		return code;
	}
	public void setCode(String code) {
		this.code = code;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getShortCut1() {
		return shortCut1;
	}
	public void setShortCut1(String shortCut1) {
		this.shortCut1 = shortCut1;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public String getTurnTo() {
		return turnTo;
	}
	public void setTurnTo(String turnTo) {
		this.turnTo = turnTo;
	}
	
	public String toString(){
		return "DIAG : "+this.id+" ;"+ this.code+";"+this.name+";"+this.shortCut1+";"+this.turnTo;
	}
}
